const dayjs = require('dayjs');
const mysql = require('mysql2');

class Builder {

    static connection = null;

    #selecct = [];
    #wheres = [];
    #join = [];
    #orders = [];
    #groups = [];
    #limit = null;
    #offset = 0;

    // 初始化链接
    initialize() {
        const connection = mysql.createPool({
            host: process.env.MYSQL_HOST,
            port: parseInt(process.env.MYSQL_PORT),
            user: process.env.MYSQL_USERNAME,
            password: process.env.MYSQL_PASSWORD,
            database: process.env.MYSQL_DATABASE,
            // rowsAsArray: true,
        });

        // connection.connect();
        return connection;
    };

    connection() {
        if(!Builder.connection) {
            try {
                Builder.connection = this.initialize().promise();
            }
            catch(error) {
                console.log('connection error');
                throw error;
            }
        }
        return Builder.connection;
    }

    async execute() {
        const sqler = this.sqler();
        console.log('sqler', sqler);
        this.cleanBuilder();
        try {
            const [result] = await this.connection().query(sqler);

            return result;
        }
        catch(error) {
            console.log('execute sql error', error.message);
            return false;
        }
    };

    async first() {
        this.offset(0).limit(1);
        const [result] = await this.execute();
        return result;
    };

    disconnection() {
        if(Builder.connection) {
            console.log('disconnection ===================');
            Builder.connection.end();
            Builder.connection = null;
        }
    };

    where(column, operate, value = undefined, split = 'and') {
        if(typeof column === 'object' && column?.length) {
            const wheres = this.whereArray(column);
            this.#wheres.push({wheres, split});
            return this;
        }
        if(value === undefined) {
            value = operate;
            operate = '=';
        }
        this.#wheres.push({column, operate, value, split});
        return this;
    }
    whereArray(columns) {
        const wheres = [];
        columns.map(([column, operate, value = undefined, split = 'and']) => {
            if(value === undefined) {
                value = operate;
                operate = '=';
            }
            wheres.push({column, operate, value, split});
        });
        return wheres;
    }

    orWhere(column, operate, value = undefined) {
        return this.where(column, operate, value, 'or');
    }

    whereIn(column, values) {
        this.#wheres.push({column, operate: 'in', value: `("${values.join('","')}")`, nowrap: true});
        return this;
    }

    orWhereIn(column, values) {
        this.#wheres.push({column, operate: 'in', value: `("${values.join('","')}")`, split: 'or', nowrap: true});
        return this;
    }

    orderBy(order) {
        this.#orders.push(order);
        return this;
    }

    join(table, on, type = 'inner') {
        this.#join.push(`${type} join ${table} on ${on}`);
        return this;
    }

    groupBy(group) {
        this.#groups.push(group);
        return this;
    }

    limit(limit) {
        this.#limit = limit;
        return this;
    }

    offset(offset) {
        this.#offset = offset;
        return this;
    }

    select(...columns) {
        this.#selecct = columns;
        return this;
    }

    sqler() {
        const where = this.builderWhere(this.#wheres).join(' ').replace(/^(or|and)\s/i, '');
        const join = this.#join.join(' ');
        const group = this.#groups.join(',');
        const order = this.#orders.join(',');
        const select = this.#selecct.join(',');
        const limit = this.limits();

        return `
            select ${select ? select : '*'}
            from ${this.table}
            ${join}
            ${where ? `where ${where}` : ''}
            ${group ? `group by ${group}` : ''}
            ${order ? `order by ${order}` : ''}
            ${limit}
        `;
    }

    cleanBuilder() {
        this.#selecct = [];
        this.#wheres = [];
        this.#join = [];
        this.#groups = [];
        this.#orders = [];
        this.#offset = 0;
        this.#limit = null;
    }

    builderWhere(wheres) {
        const lines = [];
        wheres.forEach((where) => {
            if('wheres' in where) {
                lines.push(`${where.split} (`);
                lines.push(this.builderWhere(where.wheres).join(' ').replace(/^(or|and)\s/i, ''));
                lines.push(')');
            }
            else {
                const { column, operate, value, split = 'and', nowrap = false } = where;
                if(nowrap) {
                    lines.push(`${split} ${column} ${operate} ${value}`);
                }
                else {
                    lines.push(`${split} ${column} ${operate} '${value}'`);
                }
            }
        });
        return lines;
    }
    limits() {
        let limit = '';
        if(this.#offset) {
            limit = this.#offset;
        }
        if(this.#limit) {
            if(limit) {
                limit = `${limit},${this.#limit}`;
            }
            else {
                limit = this.#limit;
            }
        }
        return limit ? `limit ${limit}` : '';
    }

    updateSqler(updates) {
        const where = this.builderWhere(this.#wheres).join(' ').replace(/^(or|and)\s/i, '');
        // 设置更新时间
        updates.updatedAt = dayjs().format('YYYY-MM-DD HH:mm:ss');
        const update = Object.entries(updates).map(([column, value]) => value === undefined ? false : `${column} = '${value}'`).filter(a => a).join(',');
        const order = this.#orders.join(',');
        const limit = this.limits();

        return `
            update ${this.table} set ${update}
            ${where ? `where ${where}` : ''}
            ${order ? `order by ${order}` : ''}
            ${limit}
        `;
    }
    async update(updates) {
        const sqler = this.updateSqler(updates);
        this.cleanBuilder();
        try {
            const [result] = await this.connection().execute(sqler);

            return result;
        }
        catch(err) {
            throw err;
        }
    }
    async insert(inserts) {
        // 设置更新创建时间
        const columns = ['createdAt', 'updatedAt'];
        const datetime = dayjs().format('YYYY-MM-DD HH:mm:ss');
        const values = [`'${datetime}'`, `'${datetime}'`];
        Object.entries(inserts).map(([column, value]) => {
            columns.push(column);
            values.push(`'${value}'`);
        });
        const sqler =  `
            insert into ${this.table} (${columns.join(',')}) values (${values.join(',')})
        `;
        console.log('insert', sqler);
        try {
            const [result] = await this.connection().execute(sqler);
            return result?.insertId;
        }
        catch(err) {
            throw err;
        }
    }
}

module.exports = Builder;